#!/usr/bin/env python
#-*- coding: UTF-8 -*- 
#set fileencodings=gb2312,utf-8
#set fileencoding=gb2312
#set termencoding=utf-8

import sys
import pymssql
reload(sys)
sys.setdefaultencoding("utf8")

config = {
			"user":"zhangyuanlong",
			"password":"Zdczhang!(yuan)*long",
			"host":"192.168.10.28",
			"database":"zdcoffice",
			"charset":"utf8",
		}

#cnx = mysql.connector.connect(user="zyl",password="zyl",host="192.168.131.62",database="zdc",charset="utf8")
cnx = pymssql.connect(**config)

cursor = cnx.cursor()

#cursor.execute("""SELECT "工号", "姓名", "性别", "fullname", "出生日期" FROM "dbo"."users" INNER JOIN "dbo"."department" ON "users"."部门" = "department"."id" WHERE "是否在用" = '是' AND "状态" = '在职' ORDER BY "工号" ASC""")
cursor.execute("""SELECT "工号", "姓名", "性别", "fullname", "出生日期","状态","deplevel","是否在用" FROM "dbo"."users" INNER JOIN "dbo"."department" ON "users"."部门" = "department"."id" WHERE "是否在用" = '是' ORDER BY "工号" ASC""")
#cursor.execute("""SELECT fullname FROM dbo.department""")
result = cursor.fetchall()

#f =file("/home/zyl/Yunio/临时文件/staff.txt","w")
f =file("/home/zyl/source/Python_Start/1k/staff.txt","w")
x = len(result)
i = 0
print "工号	姓名	性别	部门	出生日期"
#f.write("工号	姓名	性别	部门	出生日期")
while i < x:
	#print("%04d	%s	%s	%s	%s" %(result[i][0],result[i][1],result[i][2],result[i][3],result[i][4]))
	print("%04d,%s,%s,%s,%s,%s,%s,%s" %(result[i]))
	f.write("%04d,%s,%s,%s,%s,%s,%s,%s" %(result[i]))
	#f.write("%04d,%s,%s,%s" %(result[i][0],result[i][1],result[i][2],result[i][3]))
	#f.write("%04d,%s,%s,%s,%s" %(result[i]))
	f.write("\n")
	i += 1

print("%s %d" %("全公司共有人数", x))
#f.write("%s %d" %("全公司共有人数", x))
f.write("\n")
f.close()
cursor.close()
cnx.close()
